using SqlSugar;
using Rabbit3.Api.Models;

namespace Rabbit3.Api.Common
{
    /// <summary>
    /// 数据库优化配置
    /// </summary>
    public static class DatabaseOptimization
    {
        /// <summary>
        /// 创建数据库索引以提高查询性能
        /// </summary>
        public static void CreateIndexes(ISqlSugarClient db)
        {
            try
            {
                // 溯源信息表索引
                db.CodeFirst.InitTables(typeof(TraceabilityInfo));
                CreateIndexIfNotExists(db, "idx_traceability_info_deleted", "traceability_info", "IsDeleted");
                CreateIndexIfNotExists(db, "idx_traceability_info_product_name", "traceability_info", "ProductName");
                CreateIndexIfNotExists(db, "idx_traceability_info_batch", "traceability_info", "ProductionBatch");
                CreateIndexIfNotExists(db, "idx_traceability_info_enterprise", "traceability_info", "ProductionEnterprise");
                CreateIndexIfNotExists(db, "idx_traceability_info_license", "traceability_info", "ProductionLicenseNumber");
                CreateIndexIfNotExists(db, "idx_traceability_info_created_at", "traceability_info", "CreatedAt");

                // 养殖信息表索引
                db.CodeFirst.InitTables(typeof(FarmingInfo));
                CreateIndexIfNotExists(db, "idx_farming_info_traceability_id", "farming_info", "TraceabilityId");
                CreateIndexIfNotExists(db, "idx_farming_info_deleted", "farming_info", "IsDeleted");

                // 免疫信息表索引
                db.CodeFirst.InitTables(typeof(ImmunityInfo));
                CreateIndexIfNotExists(db, "idx_immunity_info_farming_id", "immunity_info", "FarmingInfoId");
                CreateIndexIfNotExists(db, "idx_immunity_info_deleted", "immunity_info", "IsDeleted");

                // 屠宰信息表索引
                db.CodeFirst.InitTables(typeof(SlaughterInfo));
                CreateIndexIfNotExists(db, "idx_slaughter_info_traceability_id", "slaughter_info", "TraceabilityId");
                CreateIndexIfNotExists(db, "idx_slaughter_info_deleted", "slaughter_info", "IsDeleted");

                // 深加工信息表索引
                db.CodeFirst.InitTables(typeof(ProcessingInfo));
                CreateIndexIfNotExists(db, "idx_processing_info_traceability_id", "processing_info", "TraceabilityId");
                CreateIndexIfNotExists(db, "idx_processing_info_deleted", "processing_info", "IsDeleted");

                // 企业信息表索引
                db.CodeFirst.InitTables(typeof(EnterpriseInfo));
                CreateIndexIfNotExists(db, "idx_enterprise_info_traceability_id", "enterprise_info", "TraceabilityId");
                CreateIndexIfNotExists(db, "idx_enterprise_info_stage_type", "enterprise_info", "StageType");
                CreateIndexIfNotExists(db, "idx_enterprise_info_deleted", "enterprise_info", "IsDeleted");

                // 供应商信息表索引
                db.CodeFirst.InitTables(typeof(SupplierInfo));
                CreateIndexIfNotExists(db, "idx_supplier_info_traceability_id", "supplier_info", "TraceabilityId");
                CreateIndexIfNotExists(db, "idx_supplier_info_type", "supplier_info", "SupplierType");
                CreateIndexIfNotExists(db, "idx_supplier_info_deleted", "supplier_info", "IsDeleted");

                Console.WriteLine("数据库索引创建完成");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建数据库索引时出错: {ex.Message}");
            }
        }

        /// <summary>
        /// 创建索引（如果不存在）
        /// </summary>
        private static void CreateIndexIfNotExists(ISqlSugarClient db, string indexName, string tableName, string columnName)
        {
            try
            {
                // 检查表是否存在
                var tableExists = db.Ado.GetDataTable($"SHOW TABLES LIKE '{tableName}'");
                if (tableExists.Rows.Count == 0)
                {
                    Console.WriteLine($"表 {tableName} 不存在，跳过索引创建");
                    return;
                }

                // 检查列是否存在
                var columnExists = db.Ado.GetDataTable($"SHOW COLUMNS FROM {tableName} LIKE '{columnName}'");
                if (columnExists.Rows.Count == 0)
                {
                    Console.WriteLine($"列 {columnName} 在表 {tableName} 中不存在，跳过索引创建");
                    return;
                }

                // 检查索引是否存在
                var indexExists = db.Ado.GetDataTable($"SHOW INDEX FROM {tableName} WHERE Key_name = '{indexName}'");
                if (indexExists.Rows.Count == 0)
                {
                    db.Ado.ExecuteCommand($"CREATE INDEX {indexName} ON {tableName}({columnName})");
                    Console.WriteLine($"成功创建索引: {indexName}");
                }
                else
                {
                    Console.WriteLine($"索引 {indexName} 已存在");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"创建索引 {indexName} 时出错: {ex.Message}");
            }
        }

    }
}
